# coding: utf-8

# 整合报价工具与opc后，对以前录入的产品进行价格数据补充

import os
import set_env_path     # 请勿删除


from sqlalchemy import create_engine
from sqlalchemy.sql import text


engine = create_engine(
    os.getenv('DATABASE_URL')
    or 'mysql+pymysql://root:@127.0.0.1/wph_www_prod?charset=utf8'
)
conn = engine.connect()


def update_system_product_price():
    """
    add by zhouhl 2017-09-22
    """

    select_product_sql = text("""
        select id,price,suggest_price from products;
    """)

    find_exists_price_sql = text("""
        select id from product_to_price
        where
            product_id=:product_id
            and user_id=0;
    """)

    update_exists_price_sql = text("""
        update product_id set
            price=:price,
            suggest_price=:suggest_price
        where
            product_id=:product_id
            and user_id=0;
    """)

    insert_exists_price_sql = text("""
        insert into product_to_price (
            user_id,product_id,price,suggest_price,
            state,recommend
        ) values (
            :user_id,:product_id,:price,:suggest_price,
            :state,:recommend
        );
    """)

    records = conn.execute(select_product_sql).fetchall()

    for i in records:
        tmp_result = conn.execute(
            find_exists_price_sql,
            product_id=i.id
        ).fetchone()
        if not tmp_result:
            conn.execute(
                insert_exists_price_sql,
                user_id=0,
                product_id=i.id,
                price=i.price,
                suggest_price=i.suggest_price,
                state=1,
                recommend=1,
            )
        else:
            conn.execute(
                update_exists_price_sql,
                price=i.price,
                suggest_price=i.suggest_price,
                product_id=i.id,
            )


if __name__ == '__main__':
    update_system_product_price()
